# coding: utf8

import pymysql
import sqlite3

db_driver = sqlite3
db_name = 'testdb'

"""
在MySql命令行客户端中创建数据库，
使用的有关命令：
> mysql -uuser -ppassword
mysql> show databases;
mysql> create database testdb;
mysql> use testdb;
mysql> show tables;
mysql> exit;
"""


def link_database(fun):
    """
    建立数据库的连接和游标对象，
    并在执行sql_fun之后关闭连接和游标
    """

    def sql_fun(table_name=None):
        # 建立数据库连接和游标
        conn = None

        if db_driver == pymysql:
            conn = pymysql.connect(
                host="localhost",
                port=3306,
                user="root",
                password="123456",
                database=db_name
            )
        if db_driver == sqlite3:
            conn = sqlite3.connect(db_name)

        cursor = conn.cursor()
        # 执行输入函数
        result = fun(cursor, table_name=table_name)
        # 关闭游标和数据库连接
        cursor.close()
        conn.commit()
        conn.close()
        return result

    return sql_fun


@link_database
def show_databases(cursor, table_name=None):
    if db_driver == pymysql:
        cursor.execute("SHOW databases")
        for x in cursor:
            print(x)


@link_database
def show_tables(cursor, table_name=None):
    if db_driver == pymysql:
        cursor.execute("SHOW tables".upper())
        for x in cursor:
            print(x)
    if db_driver == sqlite3:
        print("tables in database {}:".format(db_name))
        print("-" * 60)
        cursor.execute("select name from sqlite_master where type = 'table' order by name")
        for r in cursor.fetchall():
            print(r[0])
        print("-"*60)


@link_database
def create_table(cursor, table_name):
    sql_create = """
            CREATE TABLE {} 
            (sno char(4) primary key,
             name char(20), 
             age int,
             spec VARCHAR(100))
            """
    sqlstr = sql_create.format(table_name)
    cursor.execute(sqlstr)


@link_database
def drop_table(cursor, table_name=None):
    sql_drop = """
            DROP TABLE IF EXISTS {} 
            """
    sqlstr = sql_drop.format(table_name)
    cursor.execute(sqlstr)


@link_database
def insert(cursor, table_name=None):
    sql_insert = """
            INSERT INTO {} VALUES ('1002', '张力', 25, '绘画特长')
            """
    sqlstr = sql_insert.format(table_name)
    cursor.execute(sqlstr)


@link_database
def insert_batch(cursor, table_name=None):
    import faker
    import random
    fk = faker.Faker('zh_CN')
    specs = ['体育', '绘画', '唱歌', '书法', '摄影']
    sql_insert = "INSERT INTO {} VALUES ".format(table_name)
    record_num = 10
    for j in range(record_num):
        sql_insert += "('{:04d}', '{}', {}, '{}'),".\
            format(j+1+1000, fk.name(), fk.random.randint(15, 25), random.sample(specs, 1)[0])
    sql_insert = sql_insert[:-1]
    cursor.execute(sql_insert)


@link_database
def select(cursor, table_name=None):
    sqlstr = """
            select * from {}
            """
    sqlstr = sqlstr.format(table_name)
    cursor.execute(sqlstr)
    print("records in table {}:".format(table_name))
    print("-"*60)
    for row in cursor.fetchall():
        print(row)
    print("-"*60)


@link_database
def delete(cursor, table_name=None):
    sqlstr = """
            DELETE FROM {} WHERE sno='1001'
            """
    sqlstr = sqlstr.format(table_name)
    cursor.execute(sqlstr)


@link_database
def update(cursor, table_name=None):
    sqlstr = """
            UPDATE {} SET name='张力特' where sno='1002'
            """
    sqlstr = sqlstr.format(table_name)
    cursor.execute(sqlstr)
    for row in cursor.fetchall():
        print(row)


def task61():
    drop_table('student')
    create_table('student')
    insert('student')
    delete('student')
    update('student')


if __name__ == '__main__':
    # show_databases()
    show_tables()
    drop_table('student')

    create_table("student")
    # insert("student")
    insert_batch("student")
    select("student")
